﻿CREATE PROCEDURE [dbo].[proc_Employee_Search_DepartmentUserName]
	(
		@DeptId nvarchar(10),
		@SearchText nvarchar(50)
	)
As
Begin
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DeptId

		--;WITH ChildDepartment(ID,DName,PId) AS
		--(
		--	SELECT ID,DName,ParentID FROM department WHERE id=@DeptId
		--	UNION ALL
		--	SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
		--	INNER JOIN department ON d.ID = department.ParentID
		--)

		Select Top 15 Id,UserName,EName,
		(Select CompanyName From Company Where Id=Employee.CompanyId) As CompanyName,
		CompanyId,DepartmentId,
		Isnull((Select DName From Department Where Id=Employee.DepartmentId),'...') As DepartmentName,
		IsNull(ZhiWu,'') As ZhiWu,
		Isnull((Select PostName From Post Where Id=Employee.PostId),'...') As PostName,
		(Select Count(0) From Customer Where Customer.ProtectName=Employee.UserName And ProtectId=2) As UserPCount,
		IsNull((Select ProtectCount From Department Where Id=Employee.DepartmentId),0) As SystemPCount,
		(Select Count(0) From Customer Where Customer.ProtectName=Employee.UserName And ProtectId=4) As UserPCount2,
		IsNull((Select TrackCount From Department Where Id=Employee.DepartmentId),0) As SystemPCount2	

-- By ZHT 2015-05-11
		--GradeId,
		--(select Title from Grade where Id = GradeId) as GradeName,
		--IsNull(PostCategory,0) as PostCategory,
		--isnull((select Title from Post_Category where Post_Category.Id = PostCategory),'...') as PostCategoryName,
		--PostId,
		--Pluralist,
		--(isnull((select Wage from Grade where id=Employee.GradeId),0)) as BasicWage,
		--(isnull((select p.PostWage from Post p where p.id=Employee.PostId),0)) as PostWage,
		--(datediff(dd,ISNULL(Employee.ZzDate,GetDate()),GetDate())/365*100) as WorkWage,
		--(isnull((select JxWage from Post where id=Employee.PostId),0)) as JxWage,
		--(isnull((select ManQin from Post where id=Employee.PostId),0)) as ManQin,
		--ISNULL((Select Sum(Amount) From Employee_Allowance Where PostId=Employee.PostId and CategoryId=1 and EAId in(Select EAId From Employee_Allowance_Company Where CompanyId=Employee.CompanyId)),0) as Allowance
		--From Employee Where DepartmentId in (select id from ChildDepartment) and State <>5 and (@SearchText is null Or EName like '%'+@SearchText+'%')
		From Employee Where State <>5 And DepartmentId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
			And EName Like '%'+@SearchText+'%'
End